package com.koron.util;

import com.koron.css2.projectCenter.bean.query.PjReformQueryBean;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 工改系统JDBC
 *
 * @author 吴建宏 2021年12月8日
 */
public class JdbcConnection {

    private static Logger log = LogManager.getLogger(JdbcConnection.class);
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    private String driver;
    private String url;
    private String username;
    private String password;
    private boolean hadErrors = false;

    public JdbcConnection(String driver, String url, String username, String password) {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
    }

    public List executeList(String sql, Class<?> className, String[] objParam, Integer count) throws Exception {
        if (conn == null)
            this.createConnection();
        if (conn == null){
            return null;
        }
        this.pstmt = conn.prepareStatement(sql);
        if (count != null && count>0) {
            for (int i = 0; i < count; i++) {
                pstmt.setString(i+1,objParam[i]);
            }
        }
        rs = this.pstmt.executeQuery();
        List resultList = new ArrayList<Object>();

        //Class<?> clz = Class.forName(className);
        Class<?> clz=className;
        Field[] fs = clz.getDeclaredFields();
        int i = 0;
        while (rs.next()) {
            Object objInstrance = clz.newInstance();
            for (Field f : fs) {
                Object fieldValue = null;
                String fieldName = f.getName();

                try {
                    fieldValue = rs.getObject(fieldName);
                } catch (SQLException ex) {
                    continue;
                }
                if (fieldValue != null && fieldValue.getClass() != null) {
                    if ("java.math.BigDecimal".equals(fieldValue.getClass().getName())) {     //如果是java.math.BigDecimal类型属性，就转换为Integer,使用Integer的parseInt即可
                        fieldValue = Integer.parseInt(fieldValue.toString());
                    }
                }
                //System.out.println(fieldName+":"+fieldValue+"\t"+f.getType()+"\t"+fieldValue.getClass());
                f.setAccessible(true);
                f.set(objInstrance, fieldValue);
            }
            resultList.add(objInstrance);
        }
        return resultList;
    }

    public List executeList(String sql, Class<?> className, String[] objParam) throws Exception {
        if (conn == null)
            this.createConnection();
        if (conn == null){
            return null;
        }
        this.pstmt = conn.prepareStatement(sql);
        if (objParam != null && objParam.length>0) {
            for (int i = 0; i < objParam.length; i++) {
                pstmt.setString(i+1,objParam[i]);
            }
        }
        rs = this.pstmt.executeQuery();
        List resultList = new ArrayList<Object>();

        //Class<?> clz = Class.forName(className);
        Class<?> clz=className;
        Field[] fs = clz.getDeclaredFields();
        int i = 0;
        while (rs.next()) {
            Object objInstrance = clz.newInstance();
            for (Field f : fs) {
                Object fieldValue = null;
                String fieldName = f.getName();

                try {
                    fieldValue = rs.getObject(fieldName);
                } catch (SQLException ex) {
                    continue;
                }
                if (fieldValue != null && fieldValue.getClass() != null) {
                    if ("java.math.BigDecimal".equals(fieldValue.getClass().getName())) {     //如果是java.math.BigDecimal类型属性，就转换为Integer,使用Integer的parseInt即可
                        fieldValue = Integer.parseInt(fieldValue.toString());
                    }
                }
                //System.out.println(fieldName+":"+fieldValue+"\t"+f.getType()+"\t"+fieldValue.getClass());
                f.setAccessible(true);
                f.set(objInstrance, fieldValue);
            }
            resultList.add(objInstrance);
        }
        return resultList;
    }

    //创建数据库连接
    public boolean createConnection() {
        try {
            Class.forName(this.driver).newInstance();
            conn = DriverManager.getConnection(this.url, this.username, this.password);
        } catch (Exception e) {
            log.error("创建数据库连接"+url+"异常：",e);
            return false;
        }
        return true;
    }

    //关闭数据库连接
    public void closeDBConnection() {
        if (conn != null) {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }

    public static void main(String[] args) {

        JdbcConnection jc = new JdbcConnection("com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://36.103.227.86:3306/work_order_yinchuan?useUnicode=true&AutoReconnect=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai",
                "root", "Gdhk@3438");

        try {
            jc.createConnection();
            List<PjReformQueryBean> list = jc.executeList("SELECT `ITEMNAME` as 'XmName',DATE_FORMAT(`ITEMSTARTDATE`, '%Y-%m-%d') as 'NkgTime',`CONSTRUCTIONSITE` as 'JsPlace',`CONSTRUCTIONPROPERTY` as 'JsEssence', `ITEMLEGALDEPT` as 'XmUnit',`CONSTRUCTIONSCALEANDDESC` as 'JsDetails',`ITEMLEGALCREDITCODE` as 'UscCode', `CONTRACTPERSON` as ' LxPeople',`CONTRACTPHONE` as 'LxPhone',`HANDLEUSERNAME` as 'ClPeople',DATE_FORMAT(`VERSIONTIME`, '%Y-%m-%d') as 'CjTime',DATE_FORMAT(`FINISHTIME`, '%Y-%m-%d') as 'ClTime' FROM `nx_inf_item` a LEFT JOIN `nx_inf_apply_result` b on a.`ROWGUID` = b.`ROWGUID` ", PjReformQueryBean.class, null);
            System.out.println("----->> list.size() = " + list.size());
            if (null != list && list.size() > 0) {
//                System.out.println("---   》第一条返回结果：XmName == "+ list.get(0));
                for (int i = 0; i < list.size(); i++) {
                    System.out.println("---   》返回所有结果：ItemName == " + list.get(i));
                }

            }
            jc.closeDBConnection();
//            System.out.println(userInfoList.get(0).getUserNo());
//				System.out.println(list.size());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
